"""Advanced SQLite Query

.. helpdoc::
Basic sqlite statement evaluator.  This widget allows one to execute specific sql statements on a recieved database and generate an sql view.  Essentially always running CREATE VIEW widgetname AS statement.
"""


"""<widgetXML>
    <name>
        Advanced SQLite Query
    </name>
    <icon>
        readfile.png
    </icon>
    <summary>
        Runs the query 'QUERY', contains options for sending these data if wanted.
    </summary>
    <tags>
        <tag priority="0">
            SQLite
        </tag>
    </tags>
    <author>
        <authorname>Kyle R. Covington</authorname>
        <authorcontact>kyle@red-r.org</authorcontact>
    </author>
    </widgetXML>
"""


from OWRpy import *
import redRGUI
import sqlite3 
import re, os, redR
class advancedSQLiteQuery(OWRpy):
    def __init__(self, **kwargs):

        OWRpy.__init__(self, **kwargs)
        #self.saveSettingsList = []
        self.require_librarys(['RSQLite'])
        self.dataList = {}
        self.setRvariableNames(["view"])
        self.Rvariables['view'] = self.Rvariables['view'].replace('.', '_')  # need to get rid of the .XX at the end of system time.
        
        """.. rrsignals::"""
        self.inputs.addInput('id1', 'Main SQLite Table', signals.RedRSQLite.SQLiteDatabase, self.gotMainTable)
        
        """.. rrsignals::"""
        self.inputs.addInput('id2', 'SQLite Table', signals.RedRSQLite.SQLiteDatabase, self.gotTable, 'Multiple')
        
        """.. rrsignals::"""
        self.outputs.addOutput('sqltable', 'SQLite Table', signals.RedRSQLite.SQLiteTable)
        
        
        self.recentFiles=['Select File']
        self.database = None  # database will be obtained from the connection.  Queries neen not be done on the table that is recieved but it would make more sense if this were to happen
        
        ### GUI ###
        
        splitter = redRGUI.base.splitter(self.controlArea, orientation = 'horizontal')
        splitter.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Expanding)
        #mainBox = redRGUI.base.widgetBox(self.controlArea, orientation = 'horizontal')
        leftBox1 = splitter.widgetArea(orientation = 'vertical')
        #leftBox1.controlArea.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Expanding)
        rightBox1 = splitter.widgetArea(orientation = 'vertical')
        #rightBox1.setMaximumWidth(300)
        
        """.. rrgui::"""
        statementBox = redRGUI.base.groupBox(leftBox1, label = 'Statement', orientation = 'horizontal')
        #statementBox.controlArea.setSizePolicy(QSizePolicy.Preferred, QSizePolicy.Expanding)
        self.newTableType = redRGUI.base.comboBox(statementBox, label = 'New table function', displayLabel = False, items = ['CREATE VIEW', 'CREATE TABLE'], toolTip = 'You can make a new table from any combination of database entries.\nNew views can only be created from queries in the main database.')
        
        """.. rrgui::"""
        self.viewNameLineEdit = redRGUI.base.lineEdit(statementBox, label = 'Name', text = self.Rvariables['view'])
        
        """.. rrgui::"""
        self.statementLineEdit = redRGUI.base.lineEdit(statementBox, label = 'Statement', displayLabel = False, toolTip = 'Place your SQL statement here the actual statement that will be returned is \'CREATE VIEW XXX AS mystatement\'', callback = self.runStatement)
        
        submitButton = redRGUI.base.button(statementBox, label = 'Commit Statement', callback = self.runStatement)
        
        self.anyQueryLineEdit = redRGUI.base.lineEdit(leftBox1, label = 'Any Command', callback = self.runAnyCommand)
        
        #infoArea = redRGUI.base.groupBox(leftBox1, label = 'Info:')
        #self.infoA = redRGUI.base.widgetLabel(infoArea, 'No data connected')
        #self.infoB = redRGUI.base.widgetLabel(infoArea, "")
        outputBox = redRGUI.base.groupBox(leftBox1, label = 'Sample of Output')
        self.outputTextArea = redRGUI.base.textEdit(outputBox, label = 'Sample Output', displayLabel = False)
        self.outputTextArea.setLineWrapMode(QTextEdit.NoWrap)
        ### Table Info Section ###
        ## Table Names ##
        
        """.. rrgui::"""
        self.tableComboBox = redRGUI.base.comboBox(rightBox1, label = 'Tables', toolTip = 'Tables available in this database.  Click one to see the column names.', callback = self.resetTableNames)
        
        """.. rrgui::"""
        self.columnNameListBox = redRGUI.base.listBox(rightBox1, label = 'Column Names', toolTip = 'Column names available in this table.', callback = self.addColumnToQuery)
    
    def gotMainTable(self, data):
        if data:
            self.mainData = data
            self.runStatement()
            self.statementLineEdit.clear()
            self.resetTableNames()
        else:
            self.mainData = None
    
    def gotTable(self, data, id):
        ## set the data to the widget need to set 
        if data:
            self.dataList[id] = data
            self.resetTableNames()
        else:
            del self.dataList[id]
    def resetTableNames(self):
        if self.mainData == None:
            self.status.setText('Can\'t show table names when no main table exists.  Please resend data.')
            return
            
        conn = sqlite3.connect(self.mainData.getData())  # make the main connection
        cursor = conn.cursor()
        self.attachDBs(cursor)
        cursor.execute('SELECT * FROM SQLITE_MASTER WHERE type="table" OR type ="view"')
        info = []
        for row in cursor:  # collect the info for all of the tables and the views.
            info.append(str(row[1])+', '+ str(row[0]))
        
        conn.close()
        self.tableComboBox.update(info)
        self.resetColumnNameListBox()
    def resetColumnNameListBox(self):
        if self.mainData == '' or self.mainData == None:
            self.status.setText('Can\'t show table names when no main table exists.  Please resend data.')
            return
        tableName = self.tableComboBox.currentId().split(',')[0]
        
        conn = sqlite3.connect(self.mainData.getData())  # make the main connection
        cursor = conn.cursor()
        self.attachDBs(cursor)
        cursor.execute('PRAGMA table_info('+tableName+')')
        
        colInfo = []
        for row in cursor:
            colInfo.append(str(row[1])+', '+str(row[2]))
            
        self.columnNameListBox.update(colInfo)
        conn.close()
    
    def addColumnToQuery(self):
        ## add a column to the query statement
        tableItems = self.tableComboBox.currentId()
        tableName = tableItems.split(',')[0]
        nameItems = self.columnNameListBox.selectedIds()
        nameName = str(nameItems[0]).split(',')[0]
        self.statementLineEdit.insert(tableName+'.'+nameName)
    
    def attachDBs(self, cursor):
        """Connect all of the attached databases."""
        for v in self.dataList.values():
            try:
                dbName = os.path.split(v.getDatabase())[1].split('.')[0]
                cursor.execute('ATTACH "%s" AS %s' % (v.getDatabase(), dbName))
            except: pass  ## this might cause errors if one attempts to see data that isn't there but that's life and we'll see that some of these databases don't exist.
    def runAnyCommand(self):
        if self.mainData == None:
            self.status.setText('No Main Data connected')
            return
        if unicode(self.anyQueryLineEdit.text()) == '':
            self.status.setStatus(0)
            return
        self.outputTextArea.clear()
        
        databaseName = self.mainData.getDatabase()
        statement = unicode(self.statementLineEdit.text())
        conn = sqlite3.connect(databaseName)
        cursor = conn.cursor()
        print str(self.dataList)
        self.attachDBs(cursor)
        try:
            cursor.execute(self.anyQueryLineEdit.text())  # execute the statement
            self.outputTextArea.clear()
            self.outputTextArea.insertPlainText('\n'.join([unicode(r) for r in cursor]))
        except Exception as inst:
            self.outputTextArea.clear()
            self.outputTextArea.insertHtml('Error occured during processing, please check that the query is formatted correctly.\n')
            self.outputTextArea.insertHtml(str(inst))
            conn.close()
            return
        conn.commit()
        conn.close()
    def runStatement(self):
        if self.mainData == None:
            self.status.setText('No Main Data connected')
            return
        if unicode(self.statementLineEdit.text()) == '':
            self.status.setStatus(0)
            return
        self.outputTextArea.clear()
        
        databaseName = self.mainData.getDatabase()
        statement = unicode(self.statementLineEdit.text())
        conn = sqlite3.connect(databaseName)
        cursor = conn.cursor()
        print str(self.dataList)
        self.attachDBs(cursor)
        cursor.execute('DROP VIEW IF EXISTS '+self.viewNameLineEdit.text())
        try:
            cursor.execute(str(self.newTableType.currentText())+' '+self.viewNameLineEdit.text()+' AS '+statement)  # execute the statement
        except Exception as inst:
            self.outputTextArea.clear()
            self.outputTextArea.insertHtml('Error occured during processing, please check that the query is formatted correctly.')
            self.outputTextArea.insertHtml(str(inst))
            conn.close()
            return
        conn.commit()
        conn.close()
        newData = signals.RedRSQLite.SQLiteTable(self, data = self.viewNameLineEdit.text(), database = self.mainData.getData()) # set the new data
        self.rSend('sqltable', newData)  # send the data
        self.updateScan()
        
    def updateScan(self):
        database = self.mainData.getDatabase()
        conn = sqlite3.connect(database)
        cursor = conn.cursor()
        cursor.execute('PRAGMA table_info('+str(self.viewNameLineEdit.text())+')')
        colnames = cursor.fetchall()
        cursor.execute("SELECT * from "+str(self.viewNameLineEdit.text())+" LIMIT 10")
        data = cursor.fetchall()  # collect the first N lines of the data so that we can put it into the scan area.
        conn.close()
        txt = self.html_table(colnames,data)
        
        self.outputTextArea.insertHtml(txt)
        
    def html_table(self,colnames,data):
        s = '<table border="1" cellpadding="3">'
        s+= '  <tr>'
        for c in colnames:
            s += '<td><b>'+str(c[1])+'</b></td>'
        s+= '</tr>'
        
        for r in data:
            s += '<tr>'
            for d in r:
                s += '<td>'+str(d)+'</td>'
            s += '</tr>'
        s+= '</table>'
        return s
        